package com.homelink.android.explorerup.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DBUtil extends SQLiteOpenHelper {
	public static final String TABLE_NAME = "book_mark";
	public static final String DB_NAME = "bookmark.db";
	public static final String ID = "_id";
	public static final String NAME = "_name";
	public static final String PATH = "_path";
	public static final String TYPE = "_type";
	public static final String UPDATE_DATE = "_updated";
	public int mMaxUsedRows = 15;
	public int mMaxBookmark = 20;

	private final static int DATABASE_VERSION = 1;

	/**
	 * 
	 * @param type
	 *            Bookmark.Types
	 * @return return the select result, order by update date time
	 */
	public ArrayList<Bookmark> select(int type) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(TABLE_NAME, null, TYPE + "=?",
				new String[] { String.valueOf(type) }, null, null, UPDATE_DATE
						+ " ASC");
		ArrayList<Bookmark> result = new ArrayList<Bookmark>();
		Bookmark bookmark = null;
		while (cursor.moveToNext()) {
			bookmark = new Bookmark();
			bookmark.id = cursor.getLong(cursor.getColumnIndex(ID));
			bookmark.name = cursor.getString(cursor.getColumnIndex(NAME));
			bookmark.path = cursor.getString(cursor.getColumnIndex(PATH));
			bookmark.type = cursor.getInt(cursor.getColumnIndex(TYPE));
			bookmark.updated = cursor.getLong(cursor
					.getColumnIndex(UPDATE_DATE));
			result.add(bookmark);
			bookmark = null;
		}
		cursor.close();
		db.close();
		return result;

	}

	/**
	 * 
	 * @param type
	 * @return the delete number of rows.
	 */
	public int deleteAll(int type) {
		SQLiteDatabase db = this.getWritableDatabase();
		int result = db.delete(TABLE_NAME, TYPE + "=?",
				new String[] { Integer.toString(type) });
		return result;
	}

	public DBUtil(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
	}

	public DBUtil(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase database) {
		StringBuilder sb = new StringBuilder();
		sb.append("create table ");
		sb.append(TABLE_NAME).append(" (");
		sb.append(ID).append(" integer primary key autoincrement, ");
		sb.append(NAME).append(" text,").append(PATH);
		sb.append(" text,");
		sb.append(TYPE).append(" integer,");
		sb.append(UPDATE_DATE).append(" long);");
		database.execSQL(sb.toString());
		
	}

	/**
	 * 
	 * @param bookmark
	 * @return the row id
	 */
	public long insert(Bookmark bookmark) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(NAME, bookmark.name);
		cv.put(PATH, bookmark.path);
		cv.put(UPDATE_DATE, Long.toString(System.currentTimeMillis()));
		cv.put(TYPE, String.valueOf(bookmark.type));
		long result = db.insert(TABLE_NAME, null, cv);
		bookmark.id = result;
		db.close();
		return result;
	}

	/**
	 * 
	 * @param bookmark
	 * @return the number of update rows.
	 */
	public int update(Bookmark bookmark) {
		SQLiteDatabase db = getWritableDatabase();
		String where = ID + "= ?";
		String[] whereValues = { Long.toString(bookmark.id) };
		ContentValues cv = new ContentValues();
		cv.put(NAME, bookmark.name);
		cv.put(PATH, bookmark.path);
		cv.put(UPDATE_DATE, String.valueOf(System.currentTimeMillis()));
		cv.put(TYPE, String.valueOf(bookmark.type));
		int result = db.update(TABLE_NAME, cv, where, whereValues);
		db.close();
		return result;
	}

	/**
	 * 
	 * @param id
	 * @return the number of delete rows
	 */
	public int delete(long id) {
		SQLiteDatabase db = getWritableDatabase();
		String where = ID + " = ?";
		String[] whereValues = { Long.toString(id) };
		int result = db.delete(TABLE_NAME, where, whereValues);
		db.close();
		return result;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
		String sql = "drop table if exists " + TABLE_NAME;
		db.execSQL(sql);
		onCreate(db);
	}

	public ArrayList<Bookmark> selectAll() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db
				.query(TABLE_NAME, null, null, null, null, null, null);

		ArrayList<Bookmark> result = new ArrayList<Bookmark>();
		Bookmark bookmark = null;
		while (cursor.moveToNext()) {
			bookmark = new Bookmark();
			bookmark.id = cursor.getLong(cursor.getColumnIndex(ID));
			bookmark.name = cursor.getString(cursor.getColumnIndex(NAME));
			bookmark.path = cursor.getString(cursor.getColumnIndex(PATH));
			bookmark.type = cursor.getInt(cursor.getColumnIndex(TYPE));
			bookmark.updated = cursor.getLong(cursor
					.getColumnIndex(UPDATE_DATE));
			result.add(bookmark);
			bookmark = null;
		}
		cursor.close();
		db.close();
		return result;
	}

}
